﻿CREATE PROCEDURE [dbo].[proc_Customer_TransformTongji]
	(
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@companyid int,
		@DepartmentId int,
		@ProjectId int,
		@SourceId nvarchar(4),
		@CategoryId nvarchar(4),
		@username nvarchar(10)
	)
AS
Begin

--公司Id
--declare @companyid varchar (4)
--set @companyid = (select companyid from aspnet_Users where username=@username)

	Declare @bmbh_T  varchar(30)
	Select @bmbh_T=bmbh From department Where id=@departmentid

Select
D.Id As DepartmentId,
(case when len(bmbh)>4 then replicate('┆┄┄',(len(bmbh)-4)/2)+isnull(dname,'') else '◆'+isnull(dname,'') end) as DName,
(Select Title From OProject Where Id=@ProjectId) As Project,
(Select Count(Id) From Orders Where ProductId=@ProjectId and DepartmentId=D.Id and (select c.sourceid from customer c where orders.customerid=c.customerid)like @SourceId and (select c.stateid from customer c where orders.customerid=c.customerid)like @CategoryId and (Convert(varchar(10),FixtureDate,120)>=@StartDate and Convert(varchar(10),FixtureDate,120)<=@EndDate)) As OrdersNum,
(Select Count(Id) From Customer_Track Where GenjinId in(1,2,3) and DepartmentId=D.Id and (select c.sourceid from customer c where Customer_Track.customerid=c.customerid)like @SourceId  and (select c.stateid from customer c where Customer_Track.customerid=c.customerid)like @CategoryId  and (Convert(varchar(10),Uptime,120)>=@StartDate and Convert(varchar(10),Uptime,120)<=@EndDate)) As ValidTel,
(Select Count(Id) From CheckInOut Where MainProjectId=@ProjectId and DepartmentId=D.Id and (select c.sourceid from customer c where CheckInOut.customerid=c.customerid)like @SourceId and (select c.stateid from customer c where CheckInOut.customerid=c.customerid)like @CategoryId and (Convert(varchar(10),CheckOutTime,120)>=@StartDate and Convert(varchar(10),CheckOutTime,120)<=@EndDate)) As VisitNum,
(Select Count(Distinct(CustomerId)) From CheckInOut Where MainProjectId=@ProjectId and DepartmentId=D.Id and (select c.sourceid from customer c where CheckInOut.customerid=c.customerid)like @SourceId and (select c.stateid from customer c where CheckInOut.customerid=c.customerid)like @CategoryId and (Convert(varchar(10),CheckOutTime,120)>=@StartDate and Convert(varchar(10),CheckOutTime,120)<=@EndDate)) As VisitCustomerNum

From Department As D 
where 
	    CompanyId = @companyid
	    And Left(bmbh,Len(@bmbh_T))=@bmbh_T
Order By bmbh
End
	RETURN



